	package browser.service;

	import browser.dao.UserInfo;
	import java.io.*;
	import java.sql.*;
	import javax.servlet.*;
	import javax.servlet.http.*;

	/**
	 * Servlet that will display query results in excel
	 *
	 */
	public class QueryTableXls extends HttpServlet {

		public static Connection conn;
		public static Statement stmt;

		public void doGet(HttpServletRequest request,
				HttpServletResponse response) throws ServletException,
				IOException {

			doPost(request, response);
		}

		public void doPost(HttpServletRequest request,
				HttpServletResponse response) throws ServletException,
				IOException {

			// response.setContentType("text/html");
			response.setContentType("application/vnd.ms-excel");
			PrintWriter out = response.getWriter();

			String[] uid = request.getParameterValues("uid");
			String[] uacc = request.getParameterValues("uAccess");
			String[] tname = request.getParameterValues("tName");
			String[] qcols = request.getParameterValues("qCols");

			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
			} catch (ClassNotFoundException e) {
				out.println("Error loading the Driver:" + e.getMessage());
				return;
			}

			Connection conn = null;
			UserInfo user = MyUtilities.getUserInfo(uid[0]);

			if ((user.getUaccess() == null)
					|| (!uacc[0].equals(user.getUaccess()))) {
				out.print("Please login first");
				return;
			}
			try {
				conn = DriverManager.getConnection("jdbc:oracle:thin:"
						+ user.getUid() + "/" + user.getPassword()
						+ "@oraserver.cs.luc.edu:1521:cs");
			} catch (SQLException e1) {
				out.println("Error connecting to Oracle:" + e1.getMessage());
				return;
			}
			if (conn == null) {
				out.println("Null Connection");
				return;
			}

			Statement stmt = null;
			try {
				stmt = conn.createStatement();
			} catch (SQLException e) {
				out.println("createStatement " + e.getMessage());
				try {
					conn.close();
				} catch (SQLException e2) {
				}
				;
				return;
			}

			ResultSet rset = null;
			String qcolnames = "";
			String query = "";
			if (qcols == null) {
				query = "select * from " + tname[0];
			} else {
				for (int i = 0; i < qcols.length; i++) {
					qcolnames += qcols[i] + ",";
				}
				query = "select "
						+ qcolnames.substring(0, qcolnames.lastIndexOf(','))
						+ " from " + tname[0];
			}

			try {
				rset = stmt.executeQuery(query);
			} catch (SQLException e) {
				out.println("executeQuery " + e.getMessage());
				return;
			}

			try {
				ResultSetMetaData rsmd = rset.getMetaData();

				int colNum = rsmd.getColumnCount();
				StringBuffer sbt = new StringBuffer();
				for (int i = 1; i <= colNum; i++) {
					sbt.append(rsmd.getColumnName(i) + "\t");
				}
				out.println(sbt.toString());

				while (rset.next()) {
					StringBuffer sb = new StringBuffer();
					for (int i = 1; i <= colNum; i++) {
						sb.append(rset.getString(rsmd.getColumnName(i)) + "\t");
					}
					out.println(sb.toString());
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			out.close();
		}
	}
